package cis350.pcr;

import java.io.File;
import java.util.ArrayList;

import cis350.pcr.types.Type.SearchType;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SearchDB extends SQLiteOpenHelper {

	private final static String dbName = "Cache";

	private final String recentTable = "RecentTable";
	private final String cacheTable = "CacheTable";
	private final String semesterTable = "SemesterTable";
	private final String descriptionTable = "descriptionTable";

	private final String path = "path";
	private final String term = "term";
	private final String type = "type";
	private final String JSONData = "JSONData";

	private final String id = "id";
	private final String semester = "semester";

	private final String description = "description";

	private static int version = 1;

	private int cacheLimit = 10;

	public SearchDB(Context context) {

		super(context, dbName, null, 4);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + recentTable + " (" + path + " TEXT not null PRIMARY KEY, " + term
				+ " TEXT not null, " + type + " INTEGER not null)");
		db.execSQL("CREATE TABLE " + cacheTable + " (" + path + " TEXT not null PRIMARY KEY, " + JSONData
				+ " TEXT not null)");
		db.execSQL("CREATE TABLE " + semesterTable + " (" + id + " TEXT not null PRIMARY KEY, " + semester
				+ " TEXT not null)");
		db.execSQL("CREATE TABLE " + descriptionTable + " (" + id + " TEXT not null PRIMARY KEY, " + description
				+ " TEXT not null)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + recentTable);
		db.execSQL("DROP TABLE IF EXISTS " + cacheTable);
		db.execSQL("DROP TABLE IF EXISTS " + semesterTable);
		db.execSQL("DROP TABLE IF EXISTS " + descriptionTable);
		onCreate(db);
	}

	public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + recentTable);
		db.execSQL("DROP TABLE IF EXISTS " + cacheTable);
		db.execSQL("DROP TABLE IF EXISTS " + semesterTable);
		db.execSQL("DROP TABLE IF EXISTS " + descriptionTable);
		onCreate(db);
	}

	public void deleteDatabase(Context context) {
		Log.d("cache database", "cleared");
		context.deleteDatabase(dbName);
	}

	private void deleteOldCache() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("delete from " + recentTable + " where rowid in (select rowid from " + cacheTable
				+ " order by rowid desc limit -1 offset " + (cacheLimit - 1) + ")");
		db.close();
	}

	public void addRecent(String path, String term, SearchType type) {
		SQLiteDatabase db = this.getWritableDatabase();

		db.execSQL("delete from " + recentTable + " where path = '" + path + "'");
		db.close();
		db = this.getWritableDatabase();

		try {
			ContentValues cv = new ContentValues();
			cv.put(this.path, path);
			cv.put(this.term, term);
			cv.put(this.type, type.ordinal());
			db.insert(recentTable, null, cv);

		} catch (SQLiteConstraintException e) {
			e.printStackTrace();
		}
		db.close();
	}

	public void getRecent(ArrayList<String> path, ArrayList<String> term, ArrayList<SearchType> type) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select * from " + recentTable + " order by rowid desc", null);

		while (cur.moveToNext()) {
			path.add(cur.getString(0));
			term.add(cur.getString(1));
			type.add(SearchType.values()[cur.getInt(2)]);
		}
		cur.close();
		db.close();
	}

	public void addCache(String path, String data) {
		deleteOldCache();
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(this.path, path);
		cv.put(JSONData, data);
		db.insert(cacheTable, null, cv);
		db.close();
	}

	public String getCached(String path) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select " + JSONData + " from " + cacheTable + " where " + this.path + " = '" + path
				+ "'", null);
		String str = null;
		if (cur.moveToFirst())
			str = cur.getString(0);
		cur.close();
		db.close();
		return str;
	}

	public void addSemester(String id, String semester) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues cv = new ContentValues();
			cv.put(this.id, id);
			cv.put(this.semester, semester);
			db.insert(semesterTable, null, cv);
		} catch (SQLiteConstraintException e) {
			e.printStackTrace();
		}
		db.close();
	}

	public String getSemester(String id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select " + semester + " from " + semesterTable + " where " + this.id + " = '" + id
				+ "'", null);
		String str = null;
		if (cur.moveToFirst())
			str = cur.getString(0);
		cur.close();
		db.close();
		return str;
	}

	public void addDescription(String id, String description) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues cv = new ContentValues();
			cv.put(this.id, id);
			cv.put(this.description, description);
			db.insert(descriptionTable, null, cv);
		} catch (SQLiteConstraintException e) {
			e.printStackTrace();
		}
		db.close();
	}

	public String getDescription(String id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select " + description + " from " + descriptionTable + " where " + this.id + " = '"
				+ id + "'", null);
		String str = null;
		if (cur.moveToFirst())
			str = cur.getString(0);
		cur.close();
		db.close();
		return str;
	}

	public int getItemCount() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select count(*) from " + recentTable, null);
		int result = 0;
		if (cur.moveToFirst() && !cur.isNull(0))
			result = cur.getInt(0);
		cur.close();
		db.close();
		return result;
	}

	public long getDatabaseSize(Context context) {
		File f = context.getDatabasePath(dbName);
		long dbSize = f.length();
		return dbSize;
	}
}
